/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.dairy.database;

import com.dairy.structures.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.*;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.DataSources;
import java.util.Properties;

/**
 *
 * @author tecton
 */
public class userDatabase implements DatabaseInterface{

    private static Connection connection = null;
    private Statement stmt = null;
    private ResultSet rs = null;
    private DataSource unpooled = null;
    private DataSource pooled = null;

    public userDatabase()
    {
        try
        {
            java.lang.Class.forName("com.mysql.jdbc.Driver");
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
        try {
            // Note: your JDBC driver must be loaded [via Class.forName( ... ) or -Djdbc.properties]
            // prior to acquiring your DataSource!

            // Acquire the DataSource... this is the only c3p0 specific code here

            Properties prop = new Properties();
            prop.setProperty("user", "commonuser");
            prop.setProperty("password", "tecton");
            unpooled = DataSources.unpooledDataSource("jdbc:mysql://localhost/dairy?useUnicode=true&characterEncoding=UTF-8"
                    , prop);
            pooled = DataSources.pooledDataSource(unpooled);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public User getUser(String username)
    {
        if (username.equals(""))
            throw new UnsupportedOperationException("Not supported yet.");
//        String sql = "SELECT * FROM User WHERE username = '" + username + "'";
        String sql = "CALL getUser(" + SQLize(username) + ");";
        User result = new User();
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                rs.first();
                result.username = username;
                result.password = rs.getString("password");
                result.IDnumber = rs.getString("IDnumber");
                result.address = rs.getString("address");
                result.real_name = rs.getString("real_name");
                result.tel = rs.getString("tel");
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return result;
    }

    @Override
    public boolean registerUser(User newuser)
    {
        User user = getUser(newuser.username);
        if (user.password != null)
            return false;
        String sql = "INSERT INTO User VALUES('" + newuser.username + "','"
                + newuser.password + "'," + SQLize(newuser.real_name) + ","
                + SQLize(newuser.IDnumber) + "," + SQLize(newuser.address)
                + "," + SQLize(newuser.tel) + ");";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(sql);
            stmt.execute("commit");
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            return false;
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }
    
    @Override
    public boolean modifyInfo(User user)
    {
        String sql = "UPDATE User SET tel = '" + user.tel;
        if (user.password != null && !user.password.equals(""))
            sql += "',password = '" + user.password;
        sql += "',address = '" + user.address
                + "' WHERE username = '" + user.username + "'";
        String isolation = "set session transaction isolation level serializable;";
        String RR = "set session transaction isolation level Repeatable Read;";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(isolation);
            stmt.execute(sql);
            stmt.execute("COMMIT;");
            stmt.execute(RR);
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            return false;
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }


    @Override
    public ArrayList<Orders> getOrders(String username)
    {
        if (username.equals(""))
            throw new UnsupportedOperationException("Not supported yet.");
        ArrayList<Orders> order = new ArrayList<Orders>();
        Orders result;
        String sql = "SELECT * FROM Orders WHERE username = '" + username + "'";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                while(rs.next())
                {
                    result = new Orders();
                    result.id = rs.getInt("id");
                    result.username = rs.getString("username");
                    result.name = rs.getString("name");
                    result.job_number = rs.getInt("job_number");
                    result.day_of_week = rs.getInt("day_of_week");
                    result.duration = rs.getShort("duration");
                    result.number = rs.getInt("number");
                    order.add(result);
                }
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return order;
    }


    @Override
    public Orders getOrder(String username, String name)
    {
        if (username.equals("") || name.equals(""))
            throw new UnsupportedOperationException("Not supported yet.");
        Orders result = new Orders();
        String sql = "SELECT * FROM Orders WHERE username = '" + username + "'"
                + " AND name = '" + name + "'";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                rs.first();
                result.id = rs.getInt("id");
                result.username = rs.getString("username");
                result.name = rs.getString("name");
                result.id_company = rs.getInt("id_company");
                result.job_number = rs.getInt("job_number");
                result.day_of_week = rs.getInt("day_of_week");
                result.duration = rs.getShort("duration");
                result.number = rs.getInt("number");
                sql = "SELECT price from Sell WHERE id_company = "
                        + result.id_company + " AND name = '" + result.name + "';";
                if (stmt.execute(sql))
                {
                    rs = stmt.getResultSet();
                    rs.first();
                    result.price = rs.getDouble("price");
                }
            }
            rs.close();
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return result;
    }

    @Override
    public int getOrderMaxID()
    {
        String sql = "SELECT * FROM Orders ORDER BY id DESC";
        int id = 0;
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                rs.first();
                id = rs.getInt("id");
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return id;
    }

    @Override
    public boolean updateOrder(Orders order)
    {
        if (order == null)
            throw new UnsupportedOperationException("Not supported yet.");
        String isolation = "set session transaction isolation level serializable;";
        String RR = "set session transaction isolation level Repeatable Read;";
        String sql = "SELECT * FROM Orders WHERE id = " + order.id;
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(isolation);
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                if(rs.next())
                {
                    if (order.number <0)
                    {
                        sql = "DELETE FROM Orders" + " WHERE id = " + order.id;
                        stmt.execute("COMMIT;");
                    }
                    else
                    {
                        sql = "UPDATE Orders SET number = " + order.number
                                + ", day_of_week = " + order.day_of_week
                                + ", duration = " + order.duration
                                + " WHERE id = " + order.id;
                        stmt.execute(sql);
                        stmt.execute("COMMIT;");
                    }
                }
                else
                {
                    sql = "INSERT INTO Orders" + " VALUES (" + order.id + ",'"
                            + order.username + "','" + order.name + "'," + order.job_number
                            + "," + order.id_company + "," + order.day_of_week + "," + order.duration + ","
                            + order.number + ")";
                    stmt.execute(sql);
                    stmt.execute("COMMIT;");
                }
            }
            stmt.execute(RR);
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            return false;
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return true;
    }


    @Override
    public Company[] getWholeCompany()
    {
        ArrayList<Company> company = new ArrayList<Company>();
        Company result;
//        String sql = "SELECT id_company, company_name FROM Company;";
//        String sql = "CALL getWholeCompany();";
        String isolation = "set session transaction isolation level read uncommitted;";
        String RR = "set session transaction isolation level Repeatable Read;";
        String sql = "SELECT * FROM companyInfo;";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(isolation);
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                while(rs.next())
                {
                    result = new Company();
                    result.id_company = rs.getInt("id_company");
                    result.name = rs.getString("company_name");
                    company.add(result);
                }
            }
            stmt.execute(RR);
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        Company[] resultArray = (Company[])company.toArray(new Company[company.size()]);
        return resultArray;
    }

    @Override
    public String[] getWholeCategory()
    {
        ArrayList<String> result = new ArrayList<String>();
//        String sql = "SELECT DISTINCT category FROM DairyProduct";
//        String sql = "CALL getWholeCategory();";
        String isolation = "set session transaction isolation level read uncommitted;";
        String RR = "set session transaction isolation level Repeatable Read;";
        String sql = "SELECT * FROM categoryInfo;";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            stmt.execute(isolation);
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                while(rs.next())
                {
                    result.add(rs.getString("category"));
                }
            }
            stmt.execute(RR);
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        String[] resultArray = (String[])result.toArray(new String[result.size()]);
        return resultArray;
    }

    @Override
    public Dairyproduct[] getDairyProduct(int id_company, String category)
    {
        if (id_company < 0 && (category == null ||category.equals("")))
            throw new UnsupportedOperationException("Not supported yet.");
        ArrayList<Dairyproduct> dairyproduct = new ArrayList<Dairyproduct>();
        Dairyproduct result = new Dairyproduct();
        String sell = "SELECT * FROM company NATURAL JOIN SELL";
        String sql = "";
        if (id_company > -1)
            sell += " WHERE company.id_company = " + id_company;
        sell = "(" + sell + ") as CS";
        sql = "SELECT * FROM DairyProduct JOIN " + sell + " WHERE DairyProduct"
                + ".name = CS.name";
        if (category != null && !category.equals("null"))
            sql += " AND category = '" + category + "';";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                while(rs.next())
                {
                    result = new Dairyproduct();
                    result.name = rs.getString("name");
                    result.category = rs.getString("category");
                    result.company = rs.getString("company_name");
                    result.price = rs.getDouble("price");
                    result.property = rs.getShort("property");
                    dairyproduct.add(result);
                }
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        Dairyproduct[] resultArray = (Dairyproduct[])dairyproduct.toArray(new Dairyproduct[dairyproduct.size()]);
        return resultArray;
    }

    @Override
    public Dairyproduct[] getDairyProduct(String category)
    {
        if (category.equals(""))
            throw new UnsupportedOperationException("Not supported yet.");
        ArrayList<Dairyproduct> dairyproduct = new ArrayList<Dairyproduct>();
        Dairyproduct result = new Dairyproduct();
        String sql = "SELECT * FROM Dairyproduct NATURAL JOIN Sell NATURAL"
                + " JOIN Company";
        if (!category.equals("null"))
            sql += " WHERE category = '" + category + "'";
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                while(rs.next())
                {
                    result = new Dairyproduct();
                    result.name = rs.getString("name");
                    result.category = rs.getString("category");
                    result.company = rs.getString("company_name");
                    result.price = rs.getDouble("price");
                    result.property = rs.getShort("property");
                    dairyproduct.add(result);
                }
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        Dairyproduct[] resultArray = (Dairyproduct[])dairyproduct.toArray(new Dairyproduct[dairyproduct.size()]);
        return resultArray;
    }

    @Override
    public Dairyproduct getDairyProduct(String name_company, String name_product)
    {
        if (name_company.equals("") && name_product.equals(""))
            throw new UnsupportedOperationException("Not supported yet.");
        String companySql = "(SELECT * FROM Company WHERE company_name = '"
                + name_company + "') AS C";
        String sql = "SELECT * FROM Dairyproduct NATURAL JOIN " + companySql
                + " NATURAL JOIN Sell"+ " WHERE name='"
                + name_product + "'";
        Dairyproduct product = new Dairyproduct();
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                rs.first();
                product.category = rs.getString("category");
                product.company = rs.getString("company_name");
                product.name = rs.getString("name");
                product.property = rs.getShort("property");
                product.price = rs.getDouble("price");
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return product;
    }

    public String SQLize(String content)
    {
        if (content.equals(""))
            return "null";
        else
            return "'" + content + "'";
    }
    
    static void attemptClose(ResultSet o)
    {
	try
	    { if (o != null) o.close();}
	catch (Exception e)
	    { e.printStackTrace();}
    }

    static void attemptClose(Statement o)
    {
	try
	    { if (o != null) o.close();}
	catch (Exception e)
	    { e.printStackTrace();}
    }

    static void attemptClose(Connection o)
    {
	try
	    { if (o != null) o.close();}
	catch (Exception e)
	    { e.printStackTrace();}
    }

    @Override
    public boolean insertAdmin(Admin admin) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean insertCompany(Company company) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean insertDairyproduct(Dairyproduct dairyproduct) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean insertMilkman(Milkman milkman) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean insertOrders(Orders order) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean insertSell(Sell sell) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean insertUser(User user) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public Admin getAdmin(String username) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public Milkman getMilkman(int id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public Company getCompany(String username) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public Milkman[] getFreeMilkman(int id_company) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean modifyInfo(Milkman milkman) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean modifyInfo(Company company) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public ArrayList<Orders> getMilkmanOrders(int job_number) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public Orders[] getFreeOrder() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public double getPrice(String name_company, String name_product)
    {
        if (name_company.equals("") && name_product.equals(""))
            throw new UnsupportedOperationException("Not supported yet.");
//        String companySql = "(SELECT * FROM Company WHERE company_name = '"
//                + name_company + "') AS C";
//        String sql = "SELECT * FROM Sell NATURAL JOIN " + companySql + "WHERE name='"
//                + name_product + "'";
        String sql = "CALL getPrice(" + SQLize(name_company) + ","
                + SQLize(name_product) + ")";
        double price = 0;
        try
        {
            connection = pooled.getConnection();
            stmt = connection.createStatement();
            if (stmt.execute(sql))
            {
                rs = stmt.getResultSet();
                rs.first();
                price = rs.getDouble("price");
            }
            stmt.close();
        }
        catch (SQLException ex)
        {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            attemptClose(rs);
            attemptClose(stmt);
            attemptClose(connection);
        }
        return price;
    }

    @Override
    public boolean updateProduct(Dairyproduct product) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean dispatch(int id_order, int job_number) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void commit() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean close() {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
